﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;


namespace SQLExpressDAL
{
    public class DealRecord
    {
        public Int32 AddBuyRecord(String IID, Decimal price, int count, String userID, String stockCode)
        {
            Int32 flag = 0;

            SqlParameter[] para = new SqlParameter[]{
              new SqlParameter("@IID",SqlDbType.NVarChar,50),
              new SqlParameter("@StockCode",SqlDbType.NVarChar,50),
              new SqlParameter("@UserCode",SqlDbType.NVarChar,50),
              new SqlParameter("@Count",SqlDbType.Int),
              new SqlParameter("@Price",SqlDbType.Decimal)
            };

            para[0].Value = IID;
            para[1].Value = stockCode;
            para[2].Value = userID;
            para[3].Value = count;
            para[4].Value = price;

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalDB))
            {
                flag = (Int32)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "BuyStock", para);

            }

            return flag;
        }

        public Int32 AddSaleRecord(String IID, Decimal price, int count, String userID, String stockCode)
        {
            Int32 flag = 0;

            SqlParameter[] para = new SqlParameter[]{
              new SqlParameter("@IID",SqlDbType.NVarChar,50),
              new SqlParameter("@StockCode",SqlDbType.NVarChar,50),
              new SqlParameter("@UserCode",SqlDbType.NVarChar,50),
              new SqlParameter("@Count",SqlDbType.Int),
              new SqlParameter("@Price",SqlDbType.Decimal)
            };

            para[0].Value = IID;
            para[1].Value = stockCode;
            para[2].Value = userID;
            para[3].Value = count;
            para[4].Value = price;

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalDB))
            {
                flag = (Int32)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "SaleStock", para);

            }

            return flag;
        }

        public Hashtable BuyAdminRecord(String IID, Decimal price, int count, String userID, String stockCode)
        {
            Hashtable htab = new Hashtable();

            SqlParameter[] para = new SqlParameter[]{
              new SqlParameter("@IID",SqlDbType.NVarChar,50),
              new SqlParameter("@StockCode",SqlDbType.NVarChar,50),
              new SqlParameter("@UserCode",SqlDbType.NVarChar,50),
              new SqlParameter("@Count",SqlDbType.Int),
              new SqlParameter("@Price",SqlDbType.Decimal)
            };

            para[0].Value = IID;
            para[1].Value = stockCode;
            para[2].Value = userID;
            para[3].Value = count;
            para[4].Value = price;

            using (SqlDataReader dataReader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "BuyAdminStock", para))
            {
                if (dataReader.Read())
                {
                    htab.Add("IsSuccess", dataReader.GetValue(0));
                    htab.Add("Count", dataReader.GetValue(1));
                }


            }

            return htab;
        }


        public int AddStock(String IID, Decimal price, int count, String stockName, String stockCode)
        {
            Int32 flag = 0;

            SqlParameter[] para = new SqlParameter[]{
              new SqlParameter("@IID",SqlDbType.NVarChar,50),
              new SqlParameter("@StockCode",SqlDbType.VarChar,50),
              new SqlParameter("@StockName",SqlDbType.VarChar,50),
              new SqlParameter("@Count",SqlDbType.Int),
              new SqlParameter("@Price",SqlDbType.Money)
            };

            para[0].Value = IID;
            para[1].Value = stockCode;
            para[2].Value = stockName;
            para[3].Value = count;
            para[4].Value = price;

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalDB))
            {
                flag = (Int32)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "AddStock", para);

            }


            return flag;
        }
        public int ExChangeMoney(string fromAccount, string toAccount, string money)
        {
            Int32 flag = 0;

            SqlParameter[] para = new SqlParameter[]{
              new SqlParameter("@FromUserAccount",SqlDbType.VarChar,30),
              new SqlParameter("@ToUserAccount",SqlDbType.VarChar,30),
              new SqlParameter("@ExchangeMoney",SqlDbType.Money)
             
            };

            para[0].Value = fromAccount;
            para[1].Value = toAccount;
            para[2].Value = money;


            // flag = (Int32)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "MemberExchangeMoney", para);

            flag = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "MemberExchangeMoney", para);


            return flag;
        }
        //统计账户金额
        public Hashtable GetMemAccountMoney(String userID)
        {
            Hashtable htab = new Hashtable();

            SqlParameter[] para = new SqlParameter[]{            
              new SqlParameter("@UserID",SqlDbType.VarChar,30)   
            };

            para[0].Value = userID;

            using (SqlDataReader dataReader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "ComputeAccountMoney", para))
            {
                if (dataReader.Read())
                {
                    htab.Add("IsSuccess", dataReader.GetValue(1));
                    htab.Add("Count", dataReader.GetValue(0));
                }

            }

            return htab;

        }

        //用户股权摘要信息
        public DataTable GetUserStockSummary(string userID)
        {
            DataTable tab = new DataTable();

            DataColumn dc = new DataColumn("closeCount");
            tab.Columns.Add(dc);
            dc = new DataColumn("circulateCount");
            tab.Columns.Add(dc);
            dc = new DataColumn("salingCount");
            tab.Columns.Add(dc);
            dc = new DataColumn("price");
            tab.Columns.Add(dc);
            dc = new DataColumn("success");
            tab.Columns.Add(dc);

            DataRow dr = null;

            SqlParameter[] para = new SqlParameter[]{     
               new SqlParameter("@UserCode",SqlDbType.NVarChar,50)       
            };

            para[0].Value = userID;

            using (SqlDataReader dataReader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "ComputeStockInfo", para))
            {
                dr = tab.NewRow();

                if (dataReader.Read())
                {

                    dr[0] = dataReader.GetValue(0);
                    dr[1] = dataReader.GetValue(1);
                    dr[2] = dataReader.GetValue(2);
                    dr[3] = dataReader.GetValue(3);
                    dr[4] = dataReader.GetValue(4);

                }
                tab.Rows.Add(dr);

            }

            return tab;
        }

        //添加账户转账记录
        public Int32 AddTransferMoneyRecord(String userID, Decimal money, String flag, String fromAccount, String toAccount, String memo)
        {
            Int32 isSuccess = 0;

            SqlParameter[] para = new SqlParameter[]{
              new SqlParameter("@UserID",SqlDbType.VarChar,30),
               new SqlParameter("@Money",SqlDbType.Decimal),
              new SqlParameter("@FromAccount",SqlDbType.NVarChar,50),
              new SqlParameter("@ToAccount",SqlDbType.NVarChar,50),
              new SqlParameter("@Flag",SqlDbType.NVarChar,50),
               new SqlParameter("@Memo",SqlDbType.NVarChar,500),
             
            };

            para[0].Value = userID;
            para[1].Value = money;
            para[2].Value = fromAccount;
            para[3].Value = toAccount;
            para[4].Value = flag;
            para[5].Value = memo;

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalDB))
            {
                isSuccess = (Int32)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "TransferMoney", para);

            }

            return isSuccess;
        }

        public Int32 ComputeMemJiFei(string userID, decimal money)
        {
            Int32 isSuccess = 0;

            SqlParameter[] para = new SqlParameter[]{
              new SqlParameter("@UserID",SqlDbType.VarChar,30),
               new SqlParameter("@dealmoney",SqlDbType.Decimal) 
        
             
            };

            para[0].Value = userID;
            para[1].Value = money;


            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalDB))
            {
                isSuccess = (Int32)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "[ComputeJiFei]", para);

            }

            return isSuccess;
        }

        /// <summary>
        /// 批准提现申请
        /// </summary>
        /// <param name="userAccount"></param>
        /// <param name="cmoney"></param>
        /// <param name="recordID"></param>
        /// <param name="state"></param>
        /// <param name="operatorID"></param>
        /// <returns></returns>
        public int ApproveTiXianShengQing(String userAccount, Decimal cmoney, int recordID, int state, String operatorID)
        {
            Int32 flag = 0;

            SqlParameter[] para = new SqlParameter[]{
              new SqlParameter("@ID",SqlDbType.Int),
              new SqlParameter("@Operator",SqlDbType.VarChar,30),
              new SqlParameter("@Datetime",SqlDbType.DateTime),
              new SqlParameter("@State",SqlDbType.Int),
              new SqlParameter("@Money",SqlDbType.Money),
              new SqlParameter("@Useraccount",SqlDbType.VarChar,30)

            };

            para[0].Value = recordID;
            para[1].Value = operatorID;
            para[2].Value = DateTime.Now;
            para[3].Value = state;
            para[4].Value = cmoney;
            para[5].Value = userAccount;

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalDB))
            {
                flag = (Int32)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "ApproveTiXianApply", para);

            }


            return flag;
        }


        #region "查询记录"

        // <summary>
        /// 查询会员之间的转账记录
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <param name="flagType"></param>
        /// <returns></returns>
        public DataTable GetMemTransferRecord(string userID, string beginDate, string endDate, string flagType, string transferType)
        {
            string cmd = " select userID,cmoney,updatetime,stockfrom,case   when StockFrom='" + userID + "'  then '转出' when UserID='" + userID + "' then '转入' end as transferType from record where 1=1 ";


            List<SqlParameter> list = new List<SqlParameter>();

            if (!string.IsNullOrEmpty(userID))
            {
                // cmd+= " and stockfrom='"+userID+"'";

                if (transferType.Equals("0") || string.IsNullOrEmpty(transferType))
                {
                    cmd += " and stockfrom=@userid";
                }
                else if (transferType.Equals("1"))
                {
                    cmd += " and userid=@userid  ";
                }



                SqlParameter para = new SqlParameter("@userid", SqlDbType.NVarChar, 50);
                para.Value = userID;
                list.Add(para);
            }

            if (!string.IsNullOrEmpty(flagType))
            {
                cmd += " and flag=@flag";
                SqlParameter para = new SqlParameter("@flag", SqlDbType.NVarChar, 50);
                para.Value = flagType;

                list.Add(para);
            }

            if (!string.IsNullOrEmpty(beginDate) && !string.IsNullOrEmpty(endDate))
            {
                cmd += " and convert(varchar(10),updatetime,120)>=@begindate  and convert(varchar(10),updatetime,120)<=@endate ";
                SqlParameter para = new SqlParameter("@begindate", SqlDbType.DateTime);
                para.Value = beginDate;
                list.Add(para);
                para = new SqlParameter("@endate", SqlDbType.DateTime);
                para.Value = endDate;
                list.Add(para);
            }
            DataTable tab = new DataTable();

            DataColumn dc = new DataColumn("fromid");
            tab.Columns.Add(dc);
            dc = new DataColumn("cmoney");
            tab.Columns.Add(dc);
            dc = new DataColumn("createtime");
            tab.Columns.Add(dc);
            dc = new DataColumn("toid");
            tab.Columns.Add(dc);
            dc = new DataColumn("transferType");
            tab.Columns.Add(dc);


            DataRow dr = null;

            using (SqlDataReader dataReader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalDB, CommandType.Text, cmd, list.ToArray()))
            {

                while (dataReader.Read())
                {
                    dr = tab.NewRow();
                    dr[0] = dataReader.GetValue(3);
                    dr[1] = dataReader.GetValue(1);
                    dr[2] = dataReader.GetValue(2);
                    dr[3] = dataReader.GetValue(0);
                    dr[4] = dataReader.GetValue(4);
                    tab.Rows.Add(dr);
                }

            }

            return tab;
        }
        /// <summary>
        /// 查询会员各个账户的转账情况
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <param name="flagType"></param>
        /// <returns></returns>
        public DataTable GetMemAccountTransferRecord(string userID, string beginDate, string endDate, string flagType)
        {
            #region Table
            DataTable tab = new DataTable();

            DataColumn dc = new DataColumn("transferCount");
            tab.Columns.Add(dc);

            dc = new DataColumn("createtime");
            tab.Columns.Add(dc);

            dc = new DataColumn("memo");
            tab.Columns.Add(dc);

            dc = new DataColumn("username");
            tab.Columns.Add(dc);
            dc = new DataColumn("userid");
            tab.Columns.Add(dc);

            #endregion

            List<SqlParameter> paras = new List<SqlParameter>();

            string cmd = @" select record.transferCount,record.createtime,record.memo,users.username ,users.userid from dbo.TransferRecords as record
inner join users
on record.userid=users.userid where 1=1 ";

            if (!string.IsNullOrEmpty(userID))
            {
                cmd += " and users.userid=@userid";

                SqlParameter para = new SqlParameter("@userid", SqlDbType.NVarChar, 50);
                para.Value = userID;
                paras.Add(para);
            }

            if (!string.IsNullOrEmpty(beginDate) && !string.IsNullOrEmpty(endDate))
            {
                cmd += " and convert(varchar(10),createtime,120)>=@begindate  and convert(varchar(10),createtime,120)<=@endate ";
                SqlParameter para = new SqlParameter("@begindate", SqlDbType.DateTime);
                para.Value = beginDate;
                paras.Add(para);
                para = new SqlParameter("@endate", SqlDbType.DateTime);
                para.Value = endDate;
                paras.Add(para);
            }

            if (!string.IsNullOrEmpty(flagType))
            {
                cmd += " and flag=@flag";
                SqlParameter para = new SqlParameter("@flag", SqlDbType.NVarChar, 50);
                para.Value = flagType;
                paras.Add(para);
            }

            DataRow dr = null;

            using (SqlDataReader dataReader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalDB, CommandType.Text, cmd, paras.ToArray()))
            {

                while (dataReader.Read())
                {
                    dr = tab.NewRow();
                    dr[0] = dataReader.GetValue(0);
                    dr[1] = dataReader.GetValue(1);
                    dr[2] = dataReader.GetValue(2);
                    dr[3] = dataReader.GetValue(3);
                    dr[4] = dataReader.GetValue(4);
                    tab.Rows.Add(dr);
                }

            }

            return tab;
        }
        /// <summary>
        /// 获取提现记录
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataTable GetTiXianRecord(string userID, string beginDate, string endDate)
        {
            #region Table
            DataTable tab = new DataTable();

            DataColumn dc = new DataColumn("ID");
            tab.Columns.Add(dc);

            dc = new DataColumn("userid");
            tab.Columns.Add(dc);
            dc = new DataColumn("username");
            tab.Columns.Add(dc);
            dc = new DataColumn("cmoney");
            tab.Columns.Add(dc);
            dc = new DataColumn("createdate");
            tab.Columns.Add(dc);
            dc = new DataColumn("currstate");
            tab.Columns.Add(dc);
            dc = new DataColumn("state");
            tab.Columns.Add(dc);
            dc = new DataColumn("operator");
            tab.Columns.Add(dc);
            dc = new DataColumn("operatetime");
            tab.Columns.Add(dc);
            #endregion

            List<SqlParameter> paras = new List<SqlParameter>();
            SqlParameter para = null;
            string cmd = @" select tixian.id,users.userid,users.username,tixian.Cmoney,tixian.Createdate,case  tixian.state when 0 then '未审核' when 1 then '已通过' when 4 then '驳回' when 3 then '会员撤销' end as currstate,tixian.state as state,tixian.operator,tixian.operatetime from users 
inner join tixian on users.userid=tixian.userid where 1=1 ";

            if (!string.IsNullOrEmpty(userID))
            {
                cmd += " and users.userid=@userid";

                para = new SqlParameter("@userid", SqlDbType.NVarChar, 50);
                para.Value = userID;
                paras.Add(para);
            }

            if (!string.IsNullOrEmpty(beginDate) && !string.IsNullOrEmpty(endDate))
            {
                cmd += " and convert(varchar(10),tixian.Createdate,120)>=@begindate  and convert(varchar(10),tixian.Createdate,120)<=@endate ";
                para = new SqlParameter("@begindate", SqlDbType.DateTime);
                para.Value = beginDate;
                paras.Add(para);
                para = new SqlParameter("@endate", SqlDbType.DateTime);
                para.Value = endDate;
                paras.Add(para);
            }

            cmd += " order by createdate Desc,state Asc ";

            DataRow dr = null;

            using (SqlDataReader dataReader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalDB, CommandType.Text, cmd, paras.ToArray()))
            {

                while (dataReader.Read())
                {
                    dr = tab.NewRow();
                    dr[0] = dataReader.GetValue(0);
                    dr[1] = dataReader.GetValue(1);
                    dr[2] = dataReader.GetValue(2);
                    dr[3] = dataReader.GetValue(3);
                    dr[4] = dataReader.GetValue(4);
                    dr[5] = dataReader.GetValue(5);
                    dr[6] = dataReader.GetValue(6);
                    dr[7] = dataReader.GetValue(7);
                    dr[8] = dataReader.GetValue(8);

                    tab.Rows.Add(dr);
                }

            }


            return tab;
        }
        /// <summary>
        /// 获取会员的等级信息
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataTable GetMemDengJiInfo(string userID)
        {

            DataTable tab = null;

            SqlParameter[] para = new SqlParameter[]{
              new SqlParameter("@userid",SqlDbType.VarChar,50)             

            };

            para[0].Value = userID;

            DataSet ds = SqlHelper.FillDataSet(CommandType.StoredProcedure, "GetMemDJJinE", para);

            if (ds.Tables.Count > 0)
            {
                tab = ds.Tables[0];
            }
            return tab;

        }


        public DataTable GetDealRecord(string userID, string beginDate, string endDate, string pici)
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            string cmd = @"select T0.usercode,T1.username,case  when @pici='bdin' then T0.[count]-T0.currcount else  currcount  end as [count],T0.price,T0.createtime,T0.dealtype,T0.state
from
dealrecord as T0 inner join users as T1
on T0.usercode=T1.userid where 1=1 and T0.dealtype=case when @pici like '%in' then 'in' else 'out' end ";

            SqlParameter para = null;

            if (!string.IsNullOrEmpty(userID))
            {
                cmd += " and T0.usercode=@userID ";

                para = new SqlParameter("@userID", SqlDbType.NVarChar, 50);
                para.Value = userID;
                paras.Add(para);
            }

            if (!string.IsNullOrEmpty(beginDate) && !string.IsNullOrEmpty(endDate))
            {
                cmd += " and convert(varchar(10),T0.Createtime,120)>=@begindate  and convert(varchar(10),T0.Createtime,120)<=@endate ";
                para = new SqlParameter("@begindate", SqlDbType.DateTime);
                para.Value = beginDate;
                paras.Add(para);
                para = new SqlParameter("@endate", SqlDbType.DateTime);
                para.Value = endDate;
                paras.Add(para);
            }

            //if(pici.Contains("IN"))
            //{
            //    cmd += " and  T0.dealtype=@dealtype ";

            //    para = new SqlParameter("@dealtype", SqlDbType.VarChar,10);
            //    para.Value = "in";
            //    paras.Add(para);

            //}
            //else if (pici.Contains("OUT"))
            //{
            //    cmd += " and T0.dealtype=@dealtype ";

            //    para = new SqlParameter("@dealtype", SqlDbType.VarChar, 10);
            //    para.Value = "out";
            //    paras.Add(para);
            //}

            if (!pici.Equals("bdin"))
            {
                cmd += " and T0.currcount<>0 ";


            }

            if (!pici.Equals("all"))
            {
                para = new SqlParameter("@pici", SqlDbType.VarChar, 10);
                para.Value = pici;
                paras.Add(para);
            }

            if (pici.StartsWith("c"))
            {
                cmd += " and T0.state=@state ";

                para = new SqlParameter("@state", SqlDbType.Int);
                para.Value = 2;
                paras.Add(para);
            }
            else
            {
                cmd += " and T0.state not in (2,4) ";


            }



            return SqlHelper.FillDataSet(CommandType.Text, cmd, paras.ToArray()).Tables[0];
        }
        /// <summary>
        /// 插入充值记录
        /// </summary>
        /// <param name="userID">会员账号</param>
        /// <param name="flag">记录的类型(chongzhi)</param>
        /// <param name="cmoney">充值金额</param>
        /// <param name="recordState">充值记录的状态</param>
        /// <returns>充值记录的ID</returns>
        public Int32 InsertRechargeRecord(string userID, string flag, decimal cmoney, int recordState)
        {
            Int32 recordID = -1;

            SqlParameter[] para = new SqlParameter[]{
              new SqlParameter("@UserID",SqlDbType.VarChar,30),
               new SqlParameter("@Cmoney",SqlDbType.Money),
              new SqlParameter("@Flag",SqlDbType.VarChar,20),
              new SqlParameter("@IsDeal",SqlDbType.Int)     
             
            };

            para[0].Value = userID;
            para[1].Value = cmoney;
            para[2].Value = flag;
            para[3].Value = recordState;


            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalDB))
            {
                recordID = (Int32)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "InsertRecharge", para);

            }

            return recordID;
        }

        public DataTable GetStockBalance(string userID, string beginDate, string endDate)
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            //            string cmd = @"select T.*,T.buycount-T.salecount as holdcount ,datediff(day,T.createtime,getdate()) as holdday,(select top 1 cmoney from stockupdate order by updatetime desc)*(T.buycount-T.salecount) as shijia from (select
            // usercode,
            //isnull(sum(case when  dealtype='in' then  count-currcount end),0) as buycount,
            //isnull(sum(case when  dealtype='out'   then count-currcount end),0)   as salecount,
            //isnull(sum(case when  dealtype='out'   then  currcount end),0) as daigu ,
            //min(createtime)as createtime,
            //isnull(sum(case  when dealtype='in' then (count-currcount)*price end)-sum(case  when dealtype='out' then (count-currcount)*price end),0) chengben, 
            //isnull(sum(case  when dealtype='in' then (count-currcount)*price end)/sum(case  when dealtype='in' then (count-currcount) end),0) as unitprice
            //  from dealrecord where 1=1 and state<>2 and usercode<>'admin' ";

            string cmd = @"select T.*,T.buycount-T.salecount as holdcount ,datediff(day,T.createtime,getdate()) as holdday,( SELECT TOP 1  price FROM dbo.DealRecord WHERE DealType='out' AND State<>2 AND CurrCount<>0 ORDER BY CreateTime )*(T.buycount-T.salecount) as shijia from (select
 usercode,
isnull(sum(case when  dealtype='in' then  count-currcount end),0) as buycount,
isnull(sum(case when  dealtype='out'   then count-currcount end),0)   as salecount,
isnull(sum(case when  dealtype='out'   then  currcount end),0) as daigu ,
min(createtime)as createtime,
isnull(sum(case  when dealtype='in' then (count-currcount)*price end)-sum(case  when dealtype='out' then (count-currcount)*price end),0) chengben, 
isnull(sum(case  when dealtype='in' then (count-currcount)*price end)/sum(case  when dealtype='in' then (count-currcount) end),0) as unitprice
  from dealrecord where 1=1  and usercode<>'admin' ";


            SqlParameter para = null;

            if (!string.IsNullOrEmpty(userID))
            {
                cmd += " and  usercode=@userID ";

                para = new SqlParameter("@userID", SqlDbType.NVarChar, 50);
                para.Value = userID;
                paras.Add(para);
            }

            if (!string.IsNullOrEmpty(beginDate) && !string.IsNullOrEmpty(endDate))
            {
                cmd += " and convert(varchar(10), Createtime,120)>=@begindate  and convert(varchar(10), Createtime,120)<=@endate ";
                para = new SqlParameter("@begindate", SqlDbType.DateTime);
                para.Value = beginDate;
                paras.Add(para);
                para = new SqlParameter("@endate", SqlDbType.DateTime);
                para.Value = endDate;
                paras.Add(para);
            }

            cmd += "  group by usercode) as T  order by T.Createtime";

            return SqlHelper.FillDataSet(CommandType.Text, cmd, paras.ToArray()).Tables[0];
        }
        #endregion

        #region 更新密钥

        public int UpdateUserMiYao(string userID, string oldMiYao, string newMiYao)
        {
            Int32 flag = 0;

            SqlParameter[] para = new SqlParameter[]{
              new SqlParameter("@UserID",SqlDbType.VarChar,30),
              new SqlParameter("@OldMiYao",SqlDbType.VarChar,30),           
              new SqlParameter("@NewMiYao",SqlDbType.VarChar,30)

            };

            para[0].Value = userID;
            para[1].Value = oldMiYao;
            para[2].Value = newMiYao;

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalDB))
            {
                flag = (Int32)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "Up_MiYao", para);

            }


            return flag;
        }


        #endregion

        #region   返利规则相关

        public DataTable GetAllFanLiRule()
        {
            DataTable tab = null;
            DataSet ds = SqlHelper.FillDataSet(CommandType.Text, " SELECT * FROM dbo.FanLiRule WHERE IsActive=1 ", null);

            if (ds.Tables.Count > 0)
            {
                tab = ds.Tables[0];
            }

            return tab;

        }

        public DataTable GetSingleRuleByRecordID(string recordID)
        {
            DataTable tab = null;
            string cmd = " select  * from  FanLiRule where id=" + recordID;

            DataSet ds = SqlHelper.FillDataSet(CommandType.Text, cmd, null);

            if (ds.Tables.Count > 0)
            {
                tab = ds.Tables[0];
            }

            return tab;
        }

        public int AddNewFanLiRule(double xxValue, double sxValue, Hashtable htab)
        {
            int flag = 0;
            string cmd = "insert into FanLiRule([minValue],[MaxValue],[Field1],[Field2],[Field3],[Field4],[Field5],[Field6],[Field7],[Field8]) values(@minValue,@MaxValue,@Field1,@Field2,@Field3,@Field4,@Field5,@Field6,@Field7,@Field8)";

            List<SqlParameter> list = new List<SqlParameter>();
            SqlParameter para = null;

            para = new SqlParameter("@minValue", SqlDbType.Money);
            para.Value = xxValue;
            list.Add(para);

            para = new SqlParameter("@MaxValue", SqlDbType.Money);
            para.Value = sxValue;
            list.Add(para);


            foreach (System.Collections.DictionaryEntry entry in htab)
            {

                para = new SqlParameter(entry.Key.ToString(), SqlDbType.Float);
                para.Value = entry.Value;
                list.Add(para);

            }

            flag = SqlHelper.ExecuteNonQuery(CommandType.Text, cmd, list.ToArray());

            return flag;
        }

        public int UpdateFanLiRule(int recordID, double xxValue, double sxValue, string strDesc, Hashtable htab)
        {
            int flag = 0;
            string cmd = "update FanLiRule set [minValue]=@minValue,DESCRIPTION=@Desc, [MaxValue]=@MaxValue,  [Field1]=@Field1, [Field2]=@Field2, [Field3]=@Field3 , [Field4]=@Field4, [Field5]=@Field5, [Field6]=@Field6, [Field7]=@Field7, [Field8]=@Field8 where id=@Record";

            List<SqlParameter> list = new List<SqlParameter>();
            SqlParameter para = null;

            para = new SqlParameter("@minValue", SqlDbType.Money);
            para.Value = xxValue;
            list.Add(para);

            para = new SqlParameter("@MaxValue", SqlDbType.Money);
            para.Value = sxValue;
            list.Add(para);

            para = new SqlParameter("@Record", SqlDbType.Int);
            para.Value = recordID;
            list.Add(para);

            para = new SqlParameter("@Desc", SqlDbType.VarChar, 20);
            para.Value = strDesc;
            list.Add(para);

            foreach (System.Collections.DictionaryEntry entry in htab)
            {

                para = new SqlParameter(entry.Key.ToString(), SqlDbType.Float);
                para.Value = entry.Value;
                list.Add(para);

            }

            flag = SqlHelper.ExecuteNonQuery(CommandType.Text, cmd, list.ToArray());

            return flag;
        }

        public int DeleteFanLiRule(int recordID)
        {
            string cmd = string.Format(" delete from dbo.FanLiRule WHERE ID={0} ", recordID);

            int flag = SqlHelper.ExecuteNonQuery(CommandType.Text, cmd, null);

            return flag;
        }



        #endregion


        #region  支付宝相关

        public int UpdateOrderState(string recordID)
        {
            int flag = 0;

            SqlParameter[] para = new SqlParameter[]{
              new SqlParameter("@IID",SqlDbType.Int)        
          

            };

            para[0].Value = recordID;


            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalDB))
            {
                flag = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalDB, CommandType.StoredProcedure, "ReChargesSuccess", para);

            }


            return flag;

        }

        #endregion
    }
}
